In computing, online analytical processing, or OLAP is an method for answering multi-dimensional analytical queries swiftly. Typically OLAP is employed for ad-hoc queries and business reporting such as sales reports, marketing, management reports, budgeting, financial reporting etc. The basic element of an OLAP system is an OLAP or multi-dimensional cube (data source). It is made up of facts called measures which are categorized by dimensions. Measures are derived from the records in the fact table and dimensions are derived from the dimension tables. Measure Group in SSAS corresponds to a set of measure columns from a fact (transactional) table in its relational data source. Measure in an SSAS cube corresponds to a not key column in a fact (transactional) table in its relational data source. Measure column is typically of some numeric type. Dimension in an SSAS cube corresponds to a reference (dimensional) table in its relational data source. Dimension Attribute in an SSAS cube corresponds to either a column in a reference (dimensional) table or to a foreign key or date/time column in a transactional (fact) table in its relational data source. Dimension Attributes are quite often of string or date/time type.
Below we introduce new terms specific to object-multidimensional mapping:
Measure Property—a property of a class or column of a table or a cube measure suitable for aggregation functions.
Dimensional Property—a property of a class or a column of a table or a cube dimensional attribute most often used as a grouping (or sorting/joining/identifying) key.
OLAP tools enable users to analyze multi-dimensional data interactively from multiple perspectives using three basic analytical operations: consolidation (roll-up), drill-down, and slicing and dicing. Consolidation involves the aggregation of data in one or more dimensions. For example, all sales offices of a company are combined to forecast future sales. Drill-down is more granular and enables examination of the details. For example, the sales of a product are collected for a particular area. Slicing and dicing allows taking out (slicing) a specific set of data from the OLAP cube and examining (dicing) the slices from different viewpoints.
MDX, which was introduced by Microsoft®, is a powerful query language that can be used to query multidimensional OLAP data sources and other MDX servers such as SSAS Multidimensional, SSAS Tabular and Essbase. But MDX is a difficult language to master and thus it is difficult for the average programmer to write error free code. It is also difficult to employ for unit testing and refactoring due to a lack of tooling support. MDX is verbose when it comes to dimensional attribute and measure references specification and it does not support Object Oriented Programming (“OOP”). Additionally, MDX queries cannot be reused for data sources that do not support MDX like relational databases or in-memory objects. When MDX is used to query data from .NET applications developers have to add plumbing code (e.g. low-level code that bridges between the application and lower layers) to convert query parameters to MDX fragments and to convert query results from ADOMD.NET format into .NET object graphs.
For example, creating a reporting web-application in .NET using conventional techniques would require the following steps:
1. A developer creates classes and other data structures together with calculation and data verification/cleaning logic in .NET (domain layer, .NET environment);
2. A developer creates dynamic reporting layouts that may handle user actions like mouse clicks for data drill-down (in ASP.NET Web-forms, in ASP.NET MVC or in Silverlight; .NET environment, presentation layer);
3. A developer (typically a different developer since very few developers know both MDX and .NET well enough) creates parameterized MDX queries that filter, join, aggregate and sort data according to parameters (or builds non-parameterized MDX queries dynamically from .NET method parameters) and embeds these queries as strings into .NET code (data access layer, MS SQL Server Analysis Services environment);
4. A developer writes code that submits embedded MDX query strings into ADOMD.NET command, passes all parameters into that command and executes it; and,
5. A developer writes code that marshals the results of ADOMD.NET command execution (step 4) that are in a form of ADOMD.NET flattened record sets into a composition of associated .NET objects designed at step 1.
LINQ, which is a Microsoft®.NET Framework component that adds data querying capabilities to .NET languages, does not suffer from the same issues as MDX, but in its present form LINQ cannot be used to query OLAP data sources and other MDX servers such as SSAS and Essbase. LINQ also requires specification of aggregations, groupings, sorting and joins in each query. Such requirements complicate LINQ queries, slow down LINQ learning, adoption, query development and testing, and make LINQ queries more fragile to design changes.
In view of the foregoing, it would be advantageous to provide an improved query language. It would further be advantageous to provide such a query language that may be employed to query OLAP data sources and other MDX servers. It would be still further advantageous to provide such as query language that is relatively simple to learn and which is less prone to errors when coding. It would also be advantageous to provide such a query language that allows a single developer to perform steps 1-3 above without the developer having to perform steps 4 and 5.